Amazon QuickSight で SPICE 増分更新をした時に、データソースに発行されるクエリの条件句に設定されるタイムスタンプを検証してみた
2024.08.28いわさです。
Amazon QuickSight で SPICE モードのデータセットを使う場合は「取り込み」を意識する必要があります。
SPICE 取り込みにはフル更新と増分更新という２種類があります。
全取り込みはデータセット内のレコードを総入れ替えします。
増分取り込みは相対的に指定した範囲内のレコードのみを入れ替えします。
この増分更新の挙動ですが、増分更新設定で指定した範囲の日付列に対して増分更新対象期間のレコードのみを取得し、該当期間の分だけデータセット側のデータを削除し再作成します。
SPICE 更新スケジュールを設計する際に一定タイミング以降のデータ入れ替えを繰り返したいという場合に効果的な機能ですが、このタイミングについて厳密に調べる機会がありましたので紹介したいと思います。
増分更新時の抽出時間は何が指定される？
増分更新の公式ドキュメントは以下になります。
いくつか仕様が記載されており、例えばエンタープライズエディションの場合であれば 1 時間ごとの実行が出来たり、あるいはスケジュールによる取り込みは設定した時間から 10 分以内に実行されるなど細かい仕様が記載されています。
増分更新を設定する際にはウィンドウサイズにサイズの数値を入力することで、スケジュールに従って更新が実行される時にウィンドウサイズに従って遡ってデータを検索します。
ここで仮に 8 時間と設定し 1 時間ごとに SPICE 増分更新を実行する場合、現在が日本時間の 20 時だった場合、増分更新のクエリで指定される値は何時になると思いますか。12 時になるのでしょうか。
今回検証してわかったのですが、おそらく 18 時間前である同日の 2 時以降のデータが抽出されます。
検証結果を共有します！
検証内容
QuickSight でデータセットを作成し、増分更新の設定パターンをいくつか試してみたいと思います。
増分更新を行うためのデータソースですが今回は Athena を使いたいと思います。Athena 側でクエリの履歴も確認出来るので、QuickSight のデータソースとしてとても使いやすいです。
データセットを作成して少し放置し、取り込まれたタイミングごとのクエリ条件を確認してみます。
設定パターンを分けるために 5 つのデータセットを作成します。設定内容は次のように取り込みスケジュールの設定と増分更新の設定です。
| パターン | 頻度 | 開始時刻 | タイムゾーン | ウィンドウサイズ |
|---|---|---|---|---|
| A | 15分 | 7:15 | Asia/Tokyo | 1時間 |
| B | 15分 | 7:16 | US/Pacific | 1時間 |
| C | 30分 | 7:17 | Asia/Tokyo | 2時間 |
| D | 15分 | 7:19 | Asia/Tokyo | 1日 |
| E | 15分 | 7:19 | Asia/Tokyo | 1週間 |
上記を設定し、しばらく放置していると次のように設定した頻度でドンドンと取り込まれていきます。
QuickSight データソースで指定した Athena ワークグループを確認してみると、クエリの履歴が確認出来ます。これが QuickSight がデータセット更新のために発行したクエリです。
こいつの条件を見てみましょう。次のようなクエリが発行されています。
クエリの一番外側で増分更新で指定した日時列を対象に絞り込みを行っていますね。
/* QuickSight 1cb4e535-8785-4475-aeff-dc454c6e7f1b */
SELECT "origindt", "key", "val", "updatedt"
FROM (SELECT *, current_timestamp as updatedt FROM "default"."hoge0828") AS "hogecustomsql"
WHERE "origindt" > from_unixtime(cast(substr(cast(1724801352070 as varchar), 1, 10) AS bigint))
検証結果まとめ
データセット更新を 2 時間ほど放置した結果が出まして、先にまとめます。
まず、SPICE 更新時に指定するタイムゾーン設定は実行スケジュールに紐づくものなので増分範囲を決める情報としては使われません。
一方で実行した日時に対して UTC で解釈してデータソースにクエリを発行するので、データソース側のタイムゾーンが日本時間の場合は想定よりも 9 時間ずれることになります。
そしてウィンドウサイズは実行時間（UTC）より何時間前までのデータを入れ替え対象とするかのサイズなので、ここまでで -10時間前のデータが対象となります。
さらに、これは増分更新の実行頻度の数値だと思うのですが、実行頻度で設定した数値も加味されていました。
そのため 8/28 9:14 に実行した増分更新（頻度：15分、ウィンドウサイズ：1時間）だと-10時間15分になり、8/27 22:59 以降のデータが取り込み対象となります。
なお、参考までに実績数値は次のようになっています。
頻度：15分、タイムゾーン：+09:00、ウィンドウサイズ：1時間
| 更新実行日時 | 取り込み件数 | 更新対象日時 | 差分 |
|---|---|---|---|
| 2024年8月28日 9:14 | 24 | 2024年8月27日 22:59 | 10時間15分 |
| 2024年8月28日 8:59 | 24 | 2024年8月27日 22:44 | 10時間15分 |
| 2024年8月28日 8:44 | 24 | 2024年8月27日 22:29 | 10時間15分 |
| 2024年8月28日 8:29 | 24 | 2024年8月27日 22:14 | 10時間15分 |
| 2024年8月28日 8:14 | 24 | 2024年8月27日 21:59 | 10時間15分 |
| 2024年8月28日 7:59 | 25 | 2024年8月27日 21:44 | 10時間15分 |
| 2024年8月28日 7:44 | 25 | 2024年8月27日 21:29 | 10時間15分 |
| 2024年8月28日 7:29 | 25 | 2024年8月27日 21:14 | 10時間15分 |
| 2024年8月28日 7:14 | 25 | 2024年8月27日 20:58 | 10時間16分 |
頻度：15分、タイムゾーン：-07:00、ウィンドウサイズ：1時間
| 更新実行日時 | 取り込み件数 | 更新対象日時 | 差分 |
|---|---|---|---|
| 2024年8月28日 11:11 | 22 | 2024年8月28日 00:56:06 | 10時間15分 |
| 2024年8月28日 10:56 | 22 | 2024年8月28日 00:41:06 | 10時間15分 |
| 2024年8月28日 10:41 | 22 | 2024年8月28日 00:26:07 | 10時間15分 |
| 2024年8月28日 10:26 | 23 | 2024年8月28日 00:11:06 | 10時間15分 |
| 2024年8月28日 10:11 | 23 | 2024年8月27日 23:56:07 | 10時間14分 |
| 2024年8月28日 9:56 | 23 | 2024年8月27日 23:41:06 | 10時間15分 |
| 2024年8月28日 9:41 | 23 | 2024年8月27日 23:26:06 | 10時間15分 |
| 2024年8月28日 9:26 | 25 | 2024年8月27日 21:02:27 | 12時間23分 |
頻度：30分、タイムゾーン：+09:00、ウィンドウサイズ：2時間
| 更新実行日時 | 取り込み件数 | 更新対象日時 | 差分 |
|---|---|---|---|
| 2024年8月28日 9:17 | 25 | 2024年8月27日 21:47 | 11時間30分 |
| 2024年8月28日 8:47 | 25 | 2024年8月27日 21:17 | 11時間30分 |
| 2024年8月28日 8:17 | 25 | 2024年8月27日 20:47 | 11時間30分 |
| 2024年8月28日 7:47 | 26 | 2024年8月27日 20:17 | 11時間30分 |
| 2024年8月28日 7:17 | 26 | 2024年8月27日 20:03 | 11時間14分 |
頻度：15分、タイムゾーン：+09:00、ウィンドウサイズ：1日
| 更新実行日時 | 取り込み件数 | 更新対象日時 | 差分 |
|---|---|---|---|
| 2024年8月28日 9:34 | 37 | 2024年8月27日 0:19 | 1日9時間15分 |
| 2024年8月28日 9:19 | 37 | 2024年8月27日 0:04 | 1日9時間15分 |
| 2024年8月28日 9:04 | 37 | 2024年8月26日 23:49 | 1日9時間15分 |
| 2024年8月28日 8:49 | 37 | 2024年8月26日 23:34 | 1日9時間15分 |
| 2024年8月28日 8:34 | 37 | 2024年8月26日 23:19 | 1日9時間15分 |
| 2024年8月28日 8:19 | 37 | 2024年8月26日 23:04 | 1日9時間15分 |
| 2024年8月28日 8:04 | 37 | 2024年8月26日 22:49 | 1日9時間15分 |
| 2024年8月28日 7:49 | 37 | 2024年8月26日 22:34 | 1日9時間15分 |
| 2024年8月28日 7:34 | 37 | 2024年8月26日 22:19 | 1日9時間15分 |
| 2024年8月28日 7:19 | 37 | 2024年8月26日 22:04 | 1日9時間15分 |
頻度：15分、タイムゾーン：+09:00、ウィンドウサイズ：1週間
| 更新実行日時 | 取り込み件数 | 更新対象日時 | 差分 |
|---|---|---|---|
| 2024年8月28日 9:34 | 57 | 2024年8月21日 0:19 | 7日9時間15分 |
| 2024年8月28日 9:19 | 57 | 2024年8月21日 0:04 | 7日9時間15分 |
| 2024年8月28日 9:04 | 57 | 2024年8月20日 23:49 | 7日9時間15分 |
| 2024年8月28日 8:49 | 57 | 2024年8月20日 23:34 | 7日9時間15分 |
| 2024年8月28日 8:34 | 57 | 2024年8月20日 23:19 | 7日9時間15分 |
| 2024年8月28日 8:19 | 57 | 2024年8月20日 23:04 | 7日9時間15分 |
| 2024年8月28日 8:04 | 57 | 2024年8月20日 22:49 | 7日9時間15分 |
| 2024年8月28日 7:49 | 57 | 2024年8月20日 22:34 | 7日9時間15分 |
| 2024年8月28日 7:34 | 57 | 2024年8月20日 22:19 | 7日9時間15分 |
| 2024年8月28日 7:19 | 57 | 2024年8月20日 22:04 | 7日9時間15分 |
さいごに
本日は Amazon QuickSight で SPICE 増分更新をした時に、データソースに発行されるクエリの条件句に設定されるタイムスタンプを検証してみました。
取り込みスケジュールのタイムゾーン値はタイムスタンプでは考慮されなかったり、一方で取り込み頻度については考慮されたりと、実際に動かしてみることでどういった範囲を指定してデータソースへ増分取り込み用のクエリが発行されるのを確認することが出来ました。
増分更新の設計をする際には知っておきたい仕様です。
